from datetime import datetime

from django.db import connection
from django.db.models import Window, F, Count, Avg, When, Value, Case, IntegerField, Subquery, OuterRef, \
    ExpressionWrapper, Sum, Func
from django.db.models.functions import Rank, TruncDate, TruncMonth, ExtractMonth, ExtractYear, Coalesce
from django.forms import DurationField
from rest_framework.views import APIView

from utils.jsonResponse import SuccessResponse, ErrorResponse, DetailResponse
from .models import *
from .serializers import *


class StudentScoreByIdView(APIView):
    def get(self, request):
        student_id = int(request.GET.get('studentID'))
        courseScores = CourseScore.objects.filter(StudentID=student_id).values('TotalScore')
        # 初始化各个分段的计数器
        ranges = {
            'excellent': 0,
            'good': 0,
            'qualified': 0,
            'unqualified': 0
        }
        # 计算每个分段的数量
        total_scores = len(courseScores)  # 总成绩数量
        if (total_scores == 0):
            return ErrorResponse(msg="学生成绩为空")
        for score in courseScores:
            score_value = int(score['TotalScore'])

            if score_value >= 90:
                ranges['excellent'] += 1
            elif score_value >= 75:
                ranges['good'] += 1
            elif score_value >= 60:
                ranges['qualified'] += 1
            else:
                ranges['unqualified'] += 1

        # 计算各个分段的比例
        proportion = {key: (count / total_scores) * 100 for key, count in ranges.items()}
        return SuccessResponse(data=proportion)


class StudentBasicDataByIdView(APIView):
    def get(self, request):
        student_id = int(request.GET.get('studentID'))
        Student = StudentInfo.objects.get(StudentID=student_id)
        # 获取当前时间
        now = datetime.now()

        # 获取当前月份和年份
        current_month = now.month
        current_year = now.year

        # 判断当前学期（例如：春季学期1月到5月，秋季学期9月到12月）
        if 1 <= current_month <= 5:
            current_term = f'{current_year}春季学期'
        else:
            current_term = f'{current_year}秋季学期'
        scholarship_grants = ScholarshipGrant.objects.filter(
            StudentID=student_id,
            Type='奖学金',
            Semester=current_term
        )
        StudentData = StudentDataSerializer(Student).data
        StudentData['ClassName'] = ClassInfo.objects.filter(
            ClassID=Student.ClassName
        ).first().ClassName
        if scholarship_grants.count() != 0:
            StudentData['scholarship_grants'] = "优秀大学生"

        return SuccessResponse(data=StudentData)


class StudentRankingByIdView(APIView):
    def get(self, request):
        student_id = int(request.GET.get('studentID'))
        # 获取所有学生的成绩，并计算每个学生的排名，按 AcademicYear 和 TotalScore 排序
        courseScores = CourseScore.objects.filter().annotate(
            rank=Window(
                expression=Rank(),  # 使用 Rank() 函数为每个记录计算排名
                partition_by=[F('AcademicYear')],  # 按学年分区
                order_by=(F('TotalScore')).desc()  # 按成绩降序排序
            )
        ).order_by('AcademicYear', 'rank')  # 按学年和排名排序

        # 获取目标学生的排名
        student_rank = courseScores.filter(StudentID=student_id).values('AcademicYear', 'TotalScore', 'rank')
        return SuccessResponse(data=student_rank)


class StudentCourseNumberByIdView(APIView):
    def get(self, request):
        student_id = int(request.GET.get('studentID'))
        # 获取所有学生的成绩，并计算每个学生的排名，按 AcademicYear 和 TotalScore 排序
        courseScores = CourseScore.objects.filter(StudentID=student_id).values('AcademicYear').annotate(
            count=Count("ID")).order_by('AcademicYear')
        # 获取目标学生的排名
        return SuccessResponse(data=courseScores)


class StudentWeightedScoreByIdView(APIView):
    """
        获取某个学生的成绩，并与该专业所有学生的平均成绩合并
        """

    def get(self, request):
        student_id = int(request.GET.get('studentID'))

        # 1. 获取当前用户的专业
        student = StudentInfo.objects.filter(StudentID=student_id).first()
        if not student or not student.Major:
            return ErrorResponse(msg="未找到该学生的专业信息")

        user_major = student.Major  # 直接获取专业信息

        # 2. 获取该专业的所有学生 ID
        student_ids = StudentInfo.objects.filter(Major=user_major).values_list('StudentID', flat=True)

        # 3. 获取所有该专业学生的学年平均成绩
        course_scores = CourseScore.objects.filter(StudentID__in=student_ids).values('AcademicYear').annotate(
            average_all_students=Avg('TotalScore')  # 计算每个学年的平均成绩
        ).order_by('AcademicYear')

        # 4. 获取目标学生的学年平均成绩
        student_course_scores = CourseScore.objects.filter(StudentID=student_id).values('AcademicYear').annotate(
            average_student=Avg('TotalScore')
        ).order_by('AcademicYear')

        # 5. 处理数据，将所有学生和目标学生的成绩合并
        scores_dict = {}

        # 先填充所有学生的平均成绩
        for score in course_scores:
            academic_year = score['AcademicYear']
            scores_dict[academic_year] = {
                'AcademicYear': academic_year,
                'average_all_students': round(score['average_all_students'], 2),
                'average_student': None  # 先填充 None，后面再补充
            }

        # 再填充目标学生的成绩
        for score in student_course_scores:
            academic_year = score['AcademicYear']
            if academic_year in scores_dict:
                scores_dict[academic_year]['average_student'] = round(score['average_student'], 2)
            else:
                scores_dict[academic_year] = {
                    'AcademicYear': academic_year,
                    'average_all_students': None,  # 该学年没有其他学生数据
                    'average_student': round(score['average_student'], 2)
                }

        # 6. 转换为列表，并按照学年排序
        result = sorted(scores_dict.values(), key=lambda x: x['AcademicYear'])

        return SuccessResponse(data=result)

# 自定义数据库函数：计算时间差（秒）
class TimestampDiff(Func):
    function = 'TIMESTAMPDIFF'
    template = '%(function)s(SECOND, %(expressions)s)'  # 使用 SECOND 作为单位
    output_field = IntegerField()

class StudentLibraryTimeByIdView(APIView):
    """
    获取某个学生的借阅时长（按年月），并计算专业的平均借阅时长 过滤掉不同专业的 StudentID）
    """

    def get(self, request):
        # 1. 获取 studentID 参数
        student_id = request.GET.get('studentID')
        if not student_id or not student_id.isdigit():
            return ErrorResponse(msg="无效的 studentID")
        student_id = int(student_id)

        # 2. 获取该学生的专业
        student = StudentInfo.objects.filter(StudentID=student_id).values("Major").first()
        if not student:
            return ErrorResponse(msg="未找到该学生的专业信息")

        major_name = student["Major"]

        # 3. 获取该专业所有学生的 ID（确保不包含其他专业的学生）
        student_ids = StudentInfo.objects.filter(Major=major_name).values_list("StudentID", flat=True)

        # 4. 统计该学生的借阅数量（按年月统计）
        # 查询按年月统计的借阅次数和总时长
        library_borrows = LibraryEntry.objects.filter(
            StudentID=student_id,
        ).annotate(
            year=ExtractYear('EntryTime'),
            month=ExtractMonth('EntryTime')
        ).values('year', 'month').annotate(
            borrow_count=Count('ID'),
            total_duration_seconds=Sum(  # 直接使用数据库函数计算总时长
                TimestampDiff(F('EntryTime'), F('ExitTime'))
            )
        ).order_by('year', 'month')

        # 5. 计算该专业的平均借阅数量（先统计各学生每月借阅数量，再计算专业平均值）
        major_borrow_counts = LibraryEntry.objects.filter(
            StudentID__in=student_ids,
        ).annotate(
            year=ExtractYear('EntryTime'),
            month=ExtractMonth('EntryTime')
        ).values('year', 'month').annotate(
            borrow_count=Count('ID'),
            total_duration_seconds=Sum(  # 直接使用数据库函数计算总时长
                TimestampDiff(F('EntryTime'), F('ExitTime'))
            )
        ).order_by('year', 'month')
        # 6. 计算该专业每个月的平均借阅数量（总借阅数量/ 该专业学生总数）
        major_avg_borrow_counts = {
            f"{entry['year']}-{str(entry['month']).zfill(2)}": round(entry['total_duration_seconds'] / len(student_ids), 2)
            if len(student_ids) > 0 else 0.0  # 确保不会除以 0
            for entry in major_borrow_counts
        }

        # 7. 处理数据，将个人借阅次数与专业平均借阅次数合并
        result = []
        for borrow in library_borrows:
            formatted_year_month = f"{borrow['year']}-{str(borrow['month']).zfill(2)}"
            avg_borrow_count_value = major_avg_borrow_counts.get(formatted_year_month, 0.0)  # 确保数据格式一致

            result.append({
                'year_month': formatted_year_month,
                'borrow_count': borrow['total_duration_seconds'],
                'major_avg_borrow_count': avg_borrow_count_value
            })

        return SuccessResponse(data=result)


class StudentLibraryBorrowByIdView(APIView):
    """
    获取某个学生的借阅记录（按年月），并计算专业的平均借阅数量 过滤掉不同专业的 StudentID）
    """

    def get(self, request):
        # 1. 获取 studentID 参数
        student_id = request.GET.get('studentID')
        if not student_id or not student_id.isdigit():
            return ErrorResponse(msg="无效的 studentID")
        student_id = int(student_id)

        # 2. 获取该学生的专业
        student = StudentInfo.objects.filter(StudentID=student_id).values("Major").first()
        if not student:
            return ErrorResponse(msg="未找到该学生的专业信息")

        major_name = student["Major"]

        # 3. 获取该专业所有学生的 ID（确保不包含其他专业的学生）
        student_ids = StudentInfo.objects.filter(Major=major_name).values_list("StudentID", flat=True)

        # 4. 统计该学生的借阅数量（按年月统计）
        library_borrows = LibraryBorrow.objects.filter(StudentID=student_id).annotate(
            year=ExtractYear('Time'),
            month=ExtractMonth('Time')
        ).values('year', 'month').annotate(
            borrow_count=Count('ID')
        ).order_by('year', 'month')

        # 5. 计算该专业的平均借阅数量（先统计各学生每月借阅数量，再计算专业平均值）
        major_borrow_counts = LibraryBorrow.objects.filter(
            StudentID__in=student_ids
        ).annotate(
            year=ExtractYear('Time'),
            month=ExtractMonth('Time')
        ).values('year', 'month').annotate(
            total_borrow_count=Count('ID')  # 计算该专业学生每月的平均借阅数量
        ).order_by('year', 'month')

        # 6. 计算该专业每个月的平均借阅数量（总借阅数量/ 该专业学生总数）
        major_avg_borrow_counts = {
            f"{entry['year']}-{str(entry['month']).zfill(2)}": round(entry['total_borrow_count'] / len(student_ids), 2)
            if len(student_ids) > 0 else 0.0  # 确保不会除以 0
            for entry in major_borrow_counts
        }

        # 7. 处理数据，将个人借阅次数与专业平均借阅次数合并
        result = []
        for borrow in library_borrows:
            formatted_year_month = f"{borrow['year']}-{str(borrow['month']).zfill(2)}"
            avg_borrow_count_value = major_avg_borrow_counts.get(formatted_year_month, 0.0)  # 确保数据格式一致

            result.append({
                'year_month': formatted_year_month,
                'borrow_count': borrow['borrow_count'],
                'major_avg_borrow_count': avg_borrow_count_value
            })

        return SuccessResponse(data=result)


class StudentBorrowNumberByIdView(APIView):
    def get(self, request):
        # 1. 获取 studentID 参数
        student_id = request.GET.get('studentID')
        if not student_id or not student_id.isdigit():
            return ErrorResponse(msg="无效的 studentID")
        student_id = int(student_id)

        # 2. 获取该学生的专业
        student = StudentInfo.objects.filter(StudentID=student_id).values("Major").first()
        if not student:
            return ErrorResponse(msg="未找到该学生的专业信息")

        major_name = student["Major"]

        # 3. 获取该专业所有学生的 ID（确保不包含其他专业的学生）
        student_ids = StudentInfo.objects.filter(Major=major_name).values_list("StudentID", flat=True)
        student_count = len(student_ids)  # 计算该专业学生总数，防止除以 0

        # 4. 获取该学生的借阅记录
        student_borrow_data = LibraryEntry.objects.filter(StudentID=student_id, HasBorrowed=1).annotate(
            year=ExtractYear('ExitTime'),
            month=ExtractMonth('ExitTime')
        ).values('year', 'month').annotate(
            borrow_count=Count('ID')
        ).order_by('year', 'month')

        # 5. 获取该专业所有学生的借阅总次数（分年份和月份）
        major_borrow_data = LibraryEntry.objects.filter(StudentID__in=student_ids, HasBorrowed=1).annotate(
            year=ExtractYear('ExitTime'),
            month=ExtractMonth('ExitTime')
        ).values('year', 'month').annotate(
            total_borrow_count=Count('ID')  # 统计该专业该月总借阅次数
        ).order_by('year', 'month')

        # 6. 处理专业平均借阅次数
        major_avg_borrow_counts = {
            f"{entry['year']}-{str(entry['month']).zfill(2)}": round(entry["total_borrow_count"] / student_count, 2)
            if student_count > 0 else 0.0
            for entry in major_borrow_data
        }

        # 7. 处理数据，将个人借阅次数与专业平均借阅次数合并
        result = []
        for borrow in student_borrow_data:
            formatted_year_month = f"{borrow['year']}-{str(borrow['month']).zfill(2)}"
            avg_borrow_count_value = major_avg_borrow_counts.get(formatted_year_month, 0.0)  # 确保数据格式一致

            result.append({
                'year_month': formatted_year_month,
                'borrow_count': borrow['borrow_count'],
                'major_avg_borrow_count': avg_borrow_count_value
            })

        return SuccessResponse(data=result)


class StudentLibraryNumberByIdView(APIView):
    def get(self, request):
        # 1. 获取 studentID 参数
        student_id = request.GET.get('studentID')
        if not student_id or not student_id.isdigit():
            return ErrorResponse(msg="无效的 studentID")
        student_id = int(student_id)

        # 2. 获取该学生的专业
        student = StudentInfo.objects.filter(StudentID=student_id).values("Major").first()
        if not student:
            return ErrorResponse(msg="未找到该学生的专业信息")

        major_name = student["Major"]

        # 3. 获取该专业所有学生的 ID
        student_ids = StudentInfo.objects.filter(Major=major_name).values_list("StudentID", flat=True)
        student_count = len(student_ids)  # 计算该专业学生总数，避免除以 0

        # 4. 获取该学生的借阅记录（按年月统计）
        student_borrow_data = LibraryEntry.objects.filter(StudentID=student_id, HasBorrowed=1).annotate(
            year=ExtractYear('ExitTime'),
            month=ExtractMonth('ExitTime')
        ).values('year', 'month').annotate(
            borrow_count=Count('ID')  # 计算借阅次数
        ).order_by('year', 'month')

        # 5. 获取该专业所有学生的借阅总次数（按年月统计）
        major_borrow_data = LibraryEntry.objects.filter(StudentID__in=student_ids, HasBorrowed=1).annotate(
            year=ExtractYear('ExitTime'),
            month=ExtractMonth('ExitTime')
        ).values('year', 'month').annotate(
            total_borrow_count=Count('ID')  # 统计该专业该月总借阅次数
        ).order_by('year', 'month')

        # 6. 计算专业的平均借阅次数
        major_avg_borrow_counts = {
            f"{entry['year']}-{str(entry['month']).zfill(2)}": round(entry["total_borrow_count"] / student_count, 2)
            if student_count > 0 else 0.0
            for entry in major_borrow_data
        }

        # 7. 处理数据，将年份和月份合并成一个字段，并加入专业平均借阅次数
        result = []
        for entry in student_borrow_data:
            formatted_year_month = f"{entry['year']}-{str(entry['month']).zfill(2)}"
            avg_borrow_count_value = major_avg_borrow_counts.get(formatted_year_month, 0.0)

            result.append({
                "year_month": formatted_year_month,
                "borrow_count": entry["borrow_count"],
                "major_avg_borrow_count": avg_borrow_count_value
            })

        return SuccessResponse(data=result)


class StudentScholarshipByIdView(APIView):
    def get(self, request):
        student_id = int(request.GET.get('studentID'))
        scholarship = ScholarshipGrant.objects.filter(StudentID=student_id, Type='奖学金').values(
            "Semester", "Amount").order_by("ID")
        return SuccessResponse(data=scholarship)


class StudentStipendByIdView(APIView):
    def get(self, request):
        student_id = int(request.GET.get('studentID'))
        scholarship = ScholarshipGrant.objects.filter(StudentID=student_id, Type='助学金').values(
            "Semester", "Amount").order_by("ID")
        return SuccessResponse(data=scholarship)


class StudentGPARankingView(APIView):
    """
    获取某个学生的绩点排名（按学期）
    """

    def get(self, request):
        # 1. 获取 studentID 参数
        student_id = int(request.GET.get('studentID'))

        # 2. 查询该学生的 GPA 排名数据，并按学期排序
        gpa_ranking = StudentGPA.objects.filter(StudentID=student_id).values(
            "AcademicYear", "GPA", "MajorRank", "ClassRank"
        ).order_by("AcademicYear")

        # 3. 返回数据
        return SuccessResponse(data=gpa_ranking)


class StudentCreditInfoView(APIView):
    """
    获取某个学生在各个学年的学分信息（需要修读的学分 & 已修读的学分）
    """

    """
    获取某个学生在各个学年的学分信息（需要修读的学分 & 已修读的学分）
    """

    def get(self, request):
        student_id = int(request.GET.get('studentID'))

        # 1. 获取学生信息
        student = StudentInfo.objects.filter(StudentID=student_id).first()
        if not student or not student.Major:
            return ErrorResponse(msg="未找到该学生的专业信息")

        user_major = student.Major  # 获取专业信息

        # 2. 获取该专业各学年的所需修读学分
        required_credits = list(MajorCreditRequirement.objects.filter(Major=user_major).values(
            'AcademicYear', 'RequiredCredits'
        ))

        # 3. **使用手写 SQL 查询学生已修读的学分**
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT cs.AcademicYear, SUM(ci.Credits) AS EarnedCredits
                FROM student_CourseScore cs
                JOIN student_CourseInfo ci ON cs.CourseID = ci.CourseID
                WHERE cs.StudentID = %s AND cs.TotalScore >= 60
                GROUP BY cs.AcademicYear
                ORDER BY cs.AcademicYear;
            """, [student_id])
            completed_credits = cursor.fetchall()

        # 4. 处理数据，将所需学分与已修读学分合并
        credit_data = {}

        # 先填充所需学分数据
        for entry in required_credits:
            year = entry['AcademicYear']
            credit_data[year] = {
                'AcademicYear': year,
                'RequiredCredits': entry['RequiredCredits'],
                'EarnedCredits': 0  # 先设为 0，后续补充
            }

        # 再填充已修读的学分数据
        for year, earned_credits in completed_credits:
            if year in credit_data:
                credit_data[year]['EarnedCredits'] = earned_credits
            else:
                credit_data[year] = {
                    'AcademicYear': year,
                    'RequiredCredits': 0,  # 该学年无修读要求
                    'EarnedCredits': earned_credits
                }

        # 5. 转换为列表并排序
        result = sorted(credit_data.values(), key=lambda x: x['AcademicYear'])

        return SuccessResponse(data=result)
